package com.dao.impl;

import com.dao.NewsDao;
import com.entity.News;
import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class NewsDaoimpl implements NewsDao {
        QueryRunner runner = new QueryRunner();
    @Override
    public int insertDate(News news) {
        Connection connection = DruidUtils.getConnection();
        String sql = "insert INTO news (time,title,content,author,image,subcontent)VALUES (?,?,?,?,?,?)";
        Object[] param = new Object[]{
                news.getTime(),news.getTitle(),news.getContent(),news.getAuthor(),news.getImage(),news.getSubcontent()
        };
        try {
            int update = runner.update(connection, sql, param);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public List<News> selectDate(int pageIndex, int pageSize) {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT * from news limit ?,?";
        int offset=(pageIndex-1)*pageSize;
        try {
            List<News> query = runner.query(connection, sql, new BeanListHandler<>(News.class),offset,pageSize);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 博客详情
     * @param id
     * @return
     */
    @Override
    public News blogDetails(int id) {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT * FROM news WHERE id =?";
        try {
            News query = runner.query(connection, sql, new BeanHandler<>(News.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 最近的帖子
     * @return
     */
    @Override
    public List<News> recentPosts() {
        Connection connection = DruidUtils.getConnection();
        String sql = "SELECT id,title,image FROM news order by id DESC limit 4";
        try {
            List<News> query = runner.query(connection, sql, new BeanListHandler<>(News.class));
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public int getCount() {
        Connection connection = DruidUtils.getConnection();
        String sql = "select count(id) from news";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>());
            int count = date.intValue();
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }
}
